1.抓資料 > 匯入

CREATE DATABASE 後匯入
https://github.com/bradchao/MySQL2021/blob/master/MySQLTutor/northwind_brad.sql

2.匯入錯誤 wrpracti_northwind

建資料庫後才能放TABLE

3.語法 (抓資料)

伺服器: localhost:3306 »資料庫: northwind_v2 »資料表: employees

(1)查看職稱, FirstName, LastName 依照職稱排序, 再依照 FirstName 反向排序

SELECT Title,FirstName ,LastName FROM `employees`
ORDER BY Title ,FirstName DESC

ORDER:排序
DESC:反向
ESC:正向(不常用，預設)

(2)哪些員工不是業務代表 'Sales Representative'

SELECT Title, FirstName, LastName FROM Employees
WHERE Title <> 'Sales Representative'

WHERE:塞選(條件)

(3)哪些員工其 Region 資料為IS(或不為 IS NOT ) NULL

SELECT Title,FirstName ,LastName ,Region FROM `employees` 
WHERE Region IS NULL

SELECT Title,FirstName ,LastName ,Region FROM `employees` 
WHERE Region IS NOT NULL

WHERE:塞選(條件)

(4)英文資料適用，塞選N之後的資料

SELECT LastName FROM `employees` 
WHERE LastName >= 'n'
ORDER BY LastName

ORDER:排序
WHERE:塞選(條件)

LastName >= 'N' 是什麼意思嗎? => 78
NA, P...

(5)顯示商品名稱, 庫存量(UnitsInStock), 訂單量(UnitsOnOrder), 安全庫存量(ReorderLevel)

SELECT ProductName, UnitsInStock ,UnitsOnOrder ,ReorderLevel FROM `products`

(6)顯示商品名稱, 庫存量(UnitsInStock)少於安全庫存量(ReorderLevel)

(該進貨了)

SELECT ProductName, UnitsInStock ,UnitsOnOrder ,ReorderLevel FROM `products`
WHERE UnitsInStock <= ReorderLevel

WHERE:塞選(條件)

(7)顯示商品名稱, 庫存量(UnitsInStock)－訂單量(UnitsOnOrder)少於安全庫存量(ReorderLevel)

(該進貨了)

SELECT ProductName, UnitsInStock ,UnitsOnOrder ,ReorderLevel FROM `products`
WHERE UnitsInStock - UnitsOnOrder <= ReorderLevel

SELECT Discount FROM `orderdetails` 
WHERE Discount > 0

WHERE:塞選(條件)

伺服器: localhost:3306 »資料庫: northwind_v2 »資料表: orders

(8)訂單中顯示出：超過需求日期才寄出的訂單 RequiredDate(需求日)ShippedDate(寄出日)

(誰晚寄)

SELECT EmployeeID, OrderID, CustomerID, RequiredDate, ShippedDate FROM `Orders`
WHERE ShippedDate > RequiredDate

日期比對似字串比對
原始資料結構:datetime:只會有日期，不會有時分秒
WHERE:塞選(條件)

(9)兩個以上欄位資料相加形成一個欄位資料(字串相加，中文比較常用到)

SELECT concat(FirstName,'',LastName) FROM `employees`

concat:字串相加

伺服器: localhost:3306 »資料庫: northwind_v2 »資料表: orders

(10)計算(有先乘除後加減)

10-1

SELECT OrderID, Freight, Freight*1.1 FROM `orders`

Freight, Freightx1.1:運費、運費計算x10%(手續費)

10-2

SELECT OrderID, Freight, Freight*1.1 AS FreightTotal FROM `orders`;

FreightTotal:運費+運費計算x10%(手續費)
(AS可略)

10-3

SELECT OrderID, Freight, Freight*1.1 AS FreightTotal FROM Orders
WHERE Freight*1.1 >= 500

WHERE:塞選(條件)

(11)算出優惠價格

SELECT UnitPrice, Quantity ,UnitPrice*Quantity*(100-Discount)/100 AS Price FROM `orderdetails`

(100-Discount)/100:折扣

伺服器: localhost:3306 »資料庫: northwind_v2 »資料表: orderdetails

(12)SUM:加總

SELECT OrderID ,ProductID ,Quantity FROM `orderdetails`
WHERE ProductID = 3 ;

總銷售量

SELECT SUM(Quantity) totalQty FROM `orderdetails`
WHERE ProductID = 3 ;

SUM:加總

伺服器: localhost:3306 »資料庫: northwind_v2 »資料表: employees

(13)統計各城市的員工數量(群組後統計)

SELECT City, COUNT(EmployeeID) FROM `employees` GROUP BY City

等同

SELECT City, COUNT(EmployeeID) AS NumsEmployee FROM `employees` GROUP BY City

GROUP:群組(與ORDER不同，ORDER只是排序)
COUNT:各自有幾個

(14)只列出>1的城市

SELECT City, COUNT(EmployeeID) AS NumsEmployee FROM `employees` 
GROUP BY City
HAVING COUNT(EmployeeID) > 1

HAVING:用在GROUP BY後的篩選
COUNT:各自有幾個

(15)業務代表 'Sales Representative' 中各個城市員工數量小於 3

SELECT City, COUNT(EmployeeID) AS NumsOfEmployee FROM `employees` 
WHERE Title = 'Sales Representative'
GROUP BY City
HAVING NumsOfEmployee < 3

WHERE:條件式
COUNT:各自有幾個
HAVING:用在GROUP BY後的篩選

(16)列出員工表中的城市, 不重複

SELECT DISTINCT City FROM Employees ORDER BY City

顯示出員工表中有幾個City

SELECT COUNT(DISTINCT City) FROM Employees

COUNT:各自有幾個
DISTINCT:不同的

(17)OrderDetail 各個商品的總銷售量

SELECT ProductID, SUM(Quantity) AS TotalQty FROM OrderDetails
GROUP BY ProductID

SUM:加總
GROUP:群組(與ORDER不同，ORDER只是排序)
TotalQty:自訂

(18)OrderDetail 各個商品的總銷售量 小於 200 單位

SELECT ProductID, SUM(Quantity) AS TotalQty FROM OrderDetails GROUP BY ProductID HAVING SUM(Quantity) < 200

HAVING SUM(Quantity) < 200:群組化後的篩選 後相加 < 200

(19)統計各個商品的平均單價

SELECT ProductID, AVG(UnitPrice) AS AvgPrice FROM OrderDetails
GROUP BY ProductID
HAVING AVG(UnitPrice) > 70
ORDER BY AvgPrice

AVG:平均
AvgPrice:自訂
HAVING:用在GROUP BY後的篩選

伺服器: localhost:3306 »資料庫: northwind_v2 »資料表: orders

(20)統計各個客戶的訂單數

SELECT CustomerID, COUNT(OrderID) AS Nums FROM Orders 
GROUP BY CustomerID HAVING COUNT(OrderID) > 15
ORDER BY Nums DESC
LIMIT 5

COUNT:各自有幾個
LIMIT:只顯示幾個
Nums:自訂
DESC:反向

(21)運費取小數點一位並四捨五入

SELECT Freight, 
ROUND(Freight, 1) AS F2, 
ROUND(Freight, 2) AS F3 
FROM Orders

SELECT Freight, ROUND(Freight, 1) AS F2, 
ROUND(Freight, 2) AS F3,  
ROUND(Freight, 0) AS F4,
ROUND(Freight, -1) AS F5
FROM Orders

原始資料結構:decimal(10,4)小數點後第四位
ROUND(Freight, 1):小數點第一位
ROUND(Freight, -1):取十位數(個位數0)
F2、F3:自訂

(22)

員工入職年齡_V1

SELECT LastName, BirthDate ,BirthDate,
YEAR(HireDate)-YEAR(BirthDate) AS HireDate FROM `employees`

YEAR:年

員工入職年齡_V2

SELECT LastName, BirthDate, HireDate,
YEAR(HireDate) - YEAR(BirthDate) AS HireAge1,
ROUND(DATEDIFF(HireDate,BirthDate)/365,0) AS HireAge2 
FROM Employees

DATEDIFF:幾日
ROUBND四捨五入(,0)第幾位

(23)算幾天

SELECT DATEDIFF ('2021-03-01','2021-02-01')

DATEDIFF:幾日

(24)員工幾月生日

SELECT MONTH(BirthDate), DATE_FORMAT(BirthDate,'%m-%d-%Y') FROM Employees

SELECT FirstName, LastName,
MONTH(BirthDate) AS M1, 
DATE_FORMAT(BirthDate,'%m') AS M2
FROM Employees
ORDER BY M2

SELECT FirstName, LastName,
BirthDate,
EXTRACT(YEAR FROM BirthDate)
FROM Employees
ORDER BY EXTRACT(MONTH FROM BirthDate)

DATE_FORMAT:格式化
'%m-%d-%Y':月日年
EXTRACT:萃取

(25)子查詢 (FROM需大寫)

// Orders 中 OrderID = 10280 中的 CustomerID = ?

SELECT CustomerID FROM Orders
WHERE OrderID = 10280

// COMMI
// Customers 中 CustomerID = 'COMMI' 的公司名稱為?

// 合成以下

SELECT CompanyName FROM Customers       //查詢最終目標
WHERE CustomerID = (
	SELECT CustomerID FROM Orders   //子查詢WHERE被塞進去條件
	WHERE OrderID = 10280
)

Customers內的CustomerID 與
Orders CustomerID 有關 並從裡面抓 OrderID =10280

// 合成以下_v2

SELECT CompanyName FROM Customers        //查詢最終目標
WHERE CustomerID IN (                    //多個用IN
	SELECT CustomerID FROM Orders
	WHERE OrderID = 10280 OR OrderID = 10270 OR OrderID = 10290
)

//_v2錯誤

SELECT CompanyName FROM Customers     //查詢最終目標
WHERE CustomerID = (      //此處的=、is 都只能有一個，多個用IN
	SELECT CustomerID FROM Orders
	WHERE OrderID = 10280 OR OrderID = 10270 OR OrderID = 10290
)

(26)子查詢:1997 年中有下訂單的客戶公司名稱列出來

思考:1997 有訂單的客戶 => Group By

SELECT CompanyName FROM Customers 
WHERE CustomerID IN (
    SELECT CustomerID FROM Orders
    WHERE OrderDate BETWEEN '1997-01-01' AND '1997-12-31'
    GROUP BY CustomerID   
)

BETWEEN...AND:介於

(27)子查詢:供應商Tokyo Traders, Mayumi's, Svensk Sjfda的公司ID及供應哪些商品

公司ID

SELECT SupplierID FROM `suppliers` 
WHERE `CompanyName` IN ('Tokyo Traders',"Mayumi's",'	
Svensk Sjfda AB')

''=""

SupplierID(供應哪些商品)

SELECT productName, SupplierID 
FROM products 
WHERE `SupplierID` IN (
    SELECT SupplierID FROM `suppliers` 
    WHERE CompanyName IN 
    ('Tokyo Traders',"Mayumi's",'Svensk Sjfda AB')
)

(28)子查詢:Seafood => ?

SELECT Suppliers.CompanyName FROM Suppliers 
WHERE SupplierID IN (
    SELECT SupplierID FROM Products
    WHERE CategoryID IN (
        SELECT CategoryID FROM Categories
        WHERE Categories.CategoryName = 'Seafood'
    )
)

CompanyName在Suppliers抓
1.連接
SupplierID Suppliers
SupplierID Products
2.再連接
CategoryID Categories

(29)Join => 所有訂單的員工名字（表單內有同樣抬頭才能這樣抓）

SELECT Employees.EmployeeID, Employees.FirstName, Orders.OrderID, Orders.OrderDate
FROM Orders JOIN Employees 
ON (Employees.EmployeeID = Orders.EmployeeID)
ORDER BY Orders.OrderDate

表別名的方式:

SELECT e.EmployeeID, e.FirstName, o.OrderID, o.OrderDate
FROM Orders o JOIN Employees e 
ON (e.EmployeeID = o.EmployeeID)
ORDER BY o.OrderDate

JOIN ON:表合併

4.如果未來就業DBA 有需要甚麼證照嗎?類似MCTS或者ORACLE OCA證照嗎?

重點是寫考古題，證照順便去考就好

5.查詢語法的處理順序，為何要強調這些語法的排序？文法、語法

1. SELECT ... FROM
2. WHERE
3. GROUP BY ... HAVING(群組化後的篩選)
4. ORDER BY
5. LIMIT

6.ＳＵＭ跟ＣＯＵＮＴ差異

Sum => 數值加總
Count => 筆數加總
1 => 38
2 => 44
SELECT ProductID, COUNT(OrderID), SUM(UnitPrice), AVG(UnitPrice) FROM OrderDetails
GROUP BY ProductID
HAVING ProductID <= 2

7.w3school上面navbar還有一個SQL 跟現在在看的這邊的MySQL有差別嗎？

https://www.w3schools.com/sql/
還是有差異，SQL是統稱
https://www.w3schools.com/sql/sql_autoincrement.asp

8.作業

(1)哪些公司的總銷售額是>10000=>

SELECT c.CompanyName, SUM(od.UnitPrice * od.Quantity) AS sumPrice
FROM customers c
     JOIN orders o ON(c.CustomerID = o.CustomerID)
     JOIN orderdetails od ON ( o.OrderID = od.OrderID)
GROUP BY c.CustomerID
HAVING sumPrice > 10000
ORDER BY sumPrice DESC

customers = c
orderdetails = od
orders = o

JOIN ON一樣才能抓再一起
c.CustomerID = o.CustomerID抓在一起
o.OrderID = od.OrderID 抓在一起

(2)哪些公司的單一商品銷售額是>10000=>

SELECT c.CompanyName,o.OrderID,od.UnitPrice * od.Quantity
FROM customers c
     JOIN orders o ON(c.CustomerID = o.CustomerID)
     JOIN orderdetails od ON ( o.OrderID = od.OrderID)
WHERE od.UnitPrice * od.Quantity >10000

(3)哪些公司的單一訂單銷售額是>10000=

SELECT c.CompanyName, o.OrderID , SUM(od.UnitPrice * od.Quantity) AS sumPrice
FROM customers c
     JOIN orders o ON(c.CustomerID = o.CustomerID)
     JOIN orderdetails od ON ( o.OrderID = od.OrderID)
GROUP BY c.CustomerID , o.OrderID
HAVING sumPrice > 10000
ORDER BY sumPrice DESC

9.GROUP BY使用時機

SUM()、AVG()、COUNT()，計算都要+()